package org.sqlproc.sample.simple;

import java.sql.Statement;
import java.util.List;
import java.util.Properties;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.junit.Assert;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.sqlproc.engine.SqlCrudEngine;
import org.sqlproc.engine.SqlEngineLoader;
import org.sqlproc.engine.SqlOrder;
import org.sqlproc.engine.SqlPropertiesLoader;
import org.sqlproc.engine.SqlQueryEngine;
import org.sqlproc.sample.simple.model.Person;

public class Main {

    private final Logger logger = LoggerFactory.getLogger(getClass());

    private SessionFactory sessionFactory;
    private SqlEngineLoader sqlLoader;
    private Properties catalog;

    public Main() {
        Configuration configuration = new Configuration().configure("hibernate.cfg.xml");
        sessionFactory = configuration.buildSessionFactory();
        SqlPropertiesLoader loader = new SqlPropertiesLoader("queries.properties", this.getClass());
        sqlLoader = new SqlEngineLoader(loader.getProperties());
        SqlPropertiesLoader catalogLoader = new SqlPropertiesLoader("hsqldb_catalog.properties", this.getClass());
        catalog = catalogLoader.getProperties();
    }

    public void setupDb() throws Exception {

        Session session = null;
        Statement stmt = null;

        try {
            session = sessionFactory.openSession();
            stmt = session.connection().createStatement();
            for (int i = 1; i <= 50; i++) {
                String ddl = catalog.getProperty("s" + i);
                if (ddl == null)
                    continue;
                System.out.println(ddl);
                stmt.addBatch(ddl);
            }
            stmt.executeBatch();

        } finally {
            if (session != null)
                session.close();
        }
    }

    public List<Person> listAll() {
        SqlQueryEngine sqlEngine = sqlLoader.getQueryEngine("ALL_PEOPLE");
        Session session = null;
        try {
            session = sessionFactory.openSession();
            List<Person> list = sqlEngine.query(session, Person.class);
            logger.info("listAll size: " + list.size());
            return list;
        } finally {
            if (session != null)
                session.close();
        }
    }

    public List<Person> listSome(Person person) {
        SqlQueryEngine sqlEngine = sqlLoader.getQueryEngine("ALL_PEOPLE");
        Session session = null;
        try {
            session = sessionFactory.openSession();
            List<Person> list = sqlEngine.query(session, Person.class, person, SqlOrder.getDescOrder(2));
            logger.info("listSome size: " + list.size());
            return list;
        } finally {
            if (session != null)
                session.close();
        }
    }

    public List<Person> listLike(Person person) {
        SqlQueryEngine sqlEngine = sqlLoader.getQueryEngine("LIKE_PEOPLE");
        Session session = null;
        try {
            session = sessionFactory.openSession();
            List<Person> list = sqlEngine.query(session, Person.class, person, SqlOrder.getDescOrder(2));
            logger.info("listSome size: " + list.size());
            return list;
        } finally {
            if (session != null)
                session.close();
        }
    }

    public Person insert(Person person) {
        SqlCrudEngine sqlEngine = sqlLoader.getCrudEngine("INSERT_PERSON");
        Session session = null;
        try {
            session = sessionFactory.openSession();
            int count = sqlEngine.insert(session, person);
            logger.info("insert: " + count);
            logger.info("insert : " + person);
            return (count > 0) ? person : null;
        } finally {
            if (session != null)
                session.close();
        }
    }

    public Person get(Person person) {
        SqlCrudEngine sqlEngine = sqlLoader.getCrudEngine("GET_PERSON");
        Session session = null;
        try {
            session = sessionFactory.openSession();
            Person p = sqlEngine.get(session, Person.class, person);
            logger.info("get: " + p);
            return p;
        } finally {
            if (session != null)
                session.close();
        }
    }

    public Person update(Person person) {
        SqlCrudEngine sqlEngine = sqlLoader.getCrudEngine("UPDATE_PERSON");
        Session session = null;
        try {
            session = sessionFactory.openSession();
            int count = sqlEngine.update(session, person);
            logger.info("update: " + count);
            return (count > 0) ? person : null;
        } finally {
            if (session != null)
                session.close();
        }
    }

    public boolean delete(Person person) {
        SqlCrudEngine sqlEngine = sqlLoader.getCrudEngine("DELETE_PERSON");
        Session session = null;
        try {
            session = sessionFactory.openSession();
            int count = sqlEngine.delete(session, person);
            logger.info("delete: " + count);
            return (count > 0);
        } finally {
            if (session != null)
                session.close();
        }
    }

    public static void main(String[] args) throws Exception {
        Person person, p;
        List<Person> list;
        boolean deleted;

        Main main = new Main();
        main.setupDb();

        Long idJan = main.insert(new Person("Jan")).getId();
        Long idJanik = main.insert(new Person("Janik")).getId();
        Long idHonza = main.insert(new Person("Honza")).getId();
        Long idHonzik = main.insert(new Person("Honzik")).getId();
        Long idAndrej = main.insert(new Person("Andrej")).getId();

        list = main.listAll();
        Assert.assertEquals(5, list.size());

        person = new Person();
        person.setName("Jan");
        list = main.listSome(person);
        Assert.assertEquals(1, list.size());

        person = new Person();
        person.setName("an");
        list = main.listLike(person);
        Assert.assertEquals(3, list.size());

        person = new Person();
        person.setId(idAndrej);
        p = main.get(person);
        Assert.assertNotNull(p);
        Assert.assertEquals("Andrej", p.getName());

        person = new Person();
        person.setId(idJanik);
        person.setName("Bozena");
        p = main.update(person);
        Assert.assertNotNull(p);
        Assert.assertEquals("Bozena", p.getName());

        person = new Person();
        person.setId(idJan);
        deleted = main.delete(person);
        Assert.assertTrue(deleted);
        list = main.listAll();
        Assert.assertEquals(4, list.size());

        try {
            deleted = main.delete(null);
            Assert.fail();
        } catch (IllegalArgumentException e) {
        }
        list = main.listAll();
        Assert.assertEquals(4, list.size());
    }
}
